home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
Software Vault: The Gold Collection
/
Software Vault - The Gold Collection (American Databankers) (1993).ISO
/
cdr11
/
pdox693.zip
/
TI500.ASC
< prev
next >
Wrap
Text File
|
1993-05-04
|
6KB
|
199 lines
PRODUCT : Paradox NUMBER : 500
VERSION : 3.0 and up
OS : DOS
DATE : May 4, 1993 PAGE : 1/3
TITLE : Creating a Report with a One-To-Many Relationship
Intended Audience:
All Paradox users
Prerequisites:
You should have a thorough understanding of Paradox query and
report generation.
Purpose of the TI:
This document will illustrate how to generate a report with a
one-to-many relationship from a database that has two or more
detail tables.
In this example, the following tables will be used:
┌──────────────────┐ ┌──────────────────┐ ┌───────────────────┐
│ CUSTOMER │ │ ORDERS │ │ QUOTES │
│ ──────── │ │ ────── │ │ ────── │
│ Cust# N* │ │ Cust# N* │ │ Cust# N* │
│ Name A35 │ │ Order# N* │ │ Quote# N* │
│ Address A35 │ │ Order Item A20 │ │ Quote Item A20 │
│ City A25 │ │ Order Price $ │ │ Quote Price $ │
│ State A2 │ │ Order Qty N │ │ Quote Qty N │
│ Zip A10 │ │ │ │ Status A1 │
└──────────────────┘ └──────────────────┘ └───────────────────┘
In the above tables, each customer may have many orders and many
quotations. By manipulating these tables by using queries, you
will be able to generate a report listing the customers with
their quotations and orders grouped together. Follow these
steps:
1. Create a new table called "CUSTRPT". Borrow the structure
from "ORDERS", then move to record 6 and borrow the
structure from "QUOTES". Key the first "Cust#" field and
delete the extra "Cust#" field on row 6. You will also
need to remove the key (the "*") on the Quote# field.
PRODUCT : Paradox NUMBER : 500
VERSION : 3.0 and up
OS : DOS
DATE : May 4, 1993 PAGE : 2/3
TITLE : Creating a Report with a One-To-Many Relationship
The "CUSTRPT" table should now have the following
structure:
┌───────────────────┐
│ CUSTRPT │
│ ─────── │
│ Cust# N* │
│ Order# N* │
│ Order Item A20 │
│ Order Price $ │
│ Order Qty N │
│ Quote# N │
│ Quote Item A20 │
│ Quote Price $ │
│ Quote Qty N │
│ Status A1 │
└───────────────────┘
When you are finished creating the "CUSTRPT" table, press
[F2].
2. The next step is to perform an Insert query to insert
records from the "ORDERS" and "QUOTES" tables into
"CUSTRPT". From the Main menu, choose Ask, then select
the "CUSTRPT" table. In the left-most column, type
INSERT. Then, in each field with a corresponding field in
the "ORDERS" table, type in a unique example element using
the [F5] key. For additional information on example
elements, refer to Chapter 5 of the User's Guide (for
version 3.5 and earlier, refer to Chapter 4 of the User's
Guide).
3. Move down to the second row of "CUSTRPT" query by pressing
the down-arrow key. In the left-most column, type INSERT.
Then, in each field with a corresponding field to the
"QUOTES" table, type in a unique example element using the
[F5] key. [F5] key. For additional information on insert
queries, refer to Chapter 6 of the User's Guide (for
version 3.5 and earlier, refer to Chapter 4 of the User's
Guide).
PRODUCT : Paradox NUMBER : 500
VERSION : 3.0 and up
OS : DOS
DATE : May 4, 1993 PAGE : 3/3
TITLE : Creating a Report with a One-To-Many Relationship
4. From the Main menu, choose Ask, then select the "ORDERS"
table. In each field, type the example element from the
corresponding field from the first row of the "CUSTRPT"
query.
5. From the Main menu, choose Ask, then select the "QUOTES"
table. In each field, type the example element from the
corresponding field from the second row of the "CUSTRPT"
query.
6. Press [F2]. Paradox will insert the records from "ORDERS"
and "QUOTES" into "CUSTRPT".
7. Now, create a Free-form report on the "CUSTRPT" table. In
the report, place a group on "Cust#" (choose Group |
Insert). Then, choose Field | Lookup | Link, select the
"CUSTOMER" table, and link by the "Cust#" field. Place
all the fields from the "CUSTOMER" table in the "Cust#"
group header. Continue customizing the report to meet
your needs. For additional information on groups and
lookup, refer to Chapter 8 of the User's Guide (for
version 3.5 and earlier, refer to the Presenting Data
manual).
8. When you are finished designing your report, output your
report.
DISCLAIMER: You have the right to use this technical information
subject to the terms of the No-Nonsense License Statement that
you received with the Borland product to which this information
pertains.